import csv
import sqlite3
import os

class InitSQLiteTable:
    
    def get_conn():

        conn = sqlite3.connect('sqlite_database.db')

        return conn   

    def create_dbms_table():
        conn =InitSQLiteTable.get_conn()
        cursor = conn.cursor()

        sql="drop table t_dbms_info"
        cursor.execute(sql)
        conn.commit()

        sql = """CREATE TABLE IF NOT EXISTS t_dbms_info (
                c_id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                dbms_type TEXT,
                host TEXT,
                port INTEGER,
                user_name TEXT,
                password TEXT,
                remark TEXT

            );"""

        print(sql)
        cursor.execute(sql)
        conn.commit()

    def create_common_bill_rel_table():
        conn =InitSQLiteTable.get_conn()
        cursor = conn.cursor()

        #room_id,room_name,unit,floor,room_use,room_type,covered_area,inside_area,pool_area
        sql = """create table IF NOT EXISTS t_common_bill_rel (
                                c_id INTEGER PRIMARY KEY AUTOINCREMENT,
                                bill_type TEXT,
                                marchant TEXT,
                                product TEXT,
                                pay_type TEXT,
                                cur_status TEXT,
                                classify_id INTEGER,
                                fetch_date DATE DEFAULT CURRENT_TIMESTAMP
                            ); """

        print(sql)
        cursor.execute(sql)
        conn.commit()

    def create_pay_classify_table():
        conn =InitSQLiteTable.get_conn()
        cursor = conn.cursor()

        sql='drop table t_pay_classify'
        cursor.execute(sql)
        conn.commit()
        #room_id,room_name,unit,floor,room_use,room_type,covered_area,inside_area,pool_area
        sql = """CREATE TABLE IF NOT EXISTS `t_pay_classify` (
                    c_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    pay_type TEXT,
                    class_name TEXT,
                    subclass_name TEXT,
                    remark TEXT,
                    fetch_date DATE DEFAULT CURRENT_TIMESTAMP
                    );"""

        print(sql)
        cursor.execute(sql)
        conn.commit()

    def insert_default_data_classify_table():   
        conn =InitSQLiteTable.get_conn()
        cursor = conn.cursor()

        sql='delete from t_pay_classify'
        cursor.execute(sql)
        conn.commit()

        ###
        current_directory = os.path.dirname(os.path.abspath(__file__))
        parent_dir, name = os.path.split(current_directory)
        print(current_directory)
        file_name=parent_dir+'\\resource\\分类数据表.csv'
        with open(file_name, 'r', encoding='utf-8') as file:
            reader = csv.reader(file)
            for row in reader:  
                print(row)
                if row[0]=='类型':
                    continue
                pay_type=row[0]      
                class_name=row[1] 
                subclass_name=row[2] 
                remark=row[3] 
                sql=f"insert into t_pay_classify (pay_type,class_name,subclass_name,remark) values ('{pay_type}','{class_name}','{subclass_name}','{remark}');"
                print(sql)
                cursor.execute(sql)
                conn.commit()

    def insert_default_common_bill_rel_table():   
        conn =InitSQLiteTable.get_conn()
        cursor = conn.cursor()

        sql='delete from t_common_bill_rel'
        cursor.execute(sql)
        conn.commit()

        ###
        current_directory = os.path.dirname(os.path.abspath(__file__))
        parent_dir, name = os.path.split(current_directory)
        print(current_directory)
        file_name=parent_dir+'\\resource\\default_rel.sql'
        with open(file_name, 'r', encoding='utf-8') as file:
            lines = file.readlines()
            for line in lines:
                print(line.strip())
                cursor.execute(line.strip())
                conn.commit()

if __name__ == "__main__":

    InitSQLiteTable.create_dbms_table()
    #InitSQLiteTable.create_pay_classify_table()
    #InitSQLiteTable.insert_default_data_classify_table()
    #InitSQLiteTable.create_common_bill_rel_table()
    #InitSQLiteTable.insert_default_common_bill_rel_table()